using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
/// <summary>
/// Summary description for SupportedFacDAO
/// </summary>
public class SupportedFacDAO:DBAccess
{
    string SQL_SELECT_ONE = "select * from SupportedFacilities where ID=@Id";
    string SQL_INSERT = "insert into SupportedFacilities(FacilityID, DeparturePlaceID, ArrivalPlaceID) values(@facId,@depId,@arvId)";
    string SQL_DELETE = "delete from SupportedFacilities where ID=@id";
    string SQL_SELECT_BY_FAC = "SELECT  SupportedFacilities.ID, Facility.Name, Facility.Price, ArvLoc.Name AS ArvPlaceName, DepLoc.Name AS DepPlaceName" +
            " FROM  SupportedFacilities INNER JOIN" +
                     " Location AS ArvLoc ON SupportedFacilities.ArrivalPlaceID = ArvLoc.ID INNER JOIN" +
                      " Facility ON SupportedFacilities.FacilityID = Facility.ID INNER JOIN" +
                      " Location AS DepLoc ON SupportedFacilities.DeparturePlaceID = DepLoc.ID" +
             " WHERE Facility.ID=@facId";
	public SupportedFacDAO()
	{
		//
		// TODO: Add constructor logic here
		//
    }

    #region fields
    string message = "";
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
    }
    #endregion
    public bool IsExisted(SupportedFacility sf)
    {
        try
        {
            SqlCommand command = new SqlCommand("Select * from supportedFacilities where FacilityID=@facId and DeparturePlaceID=@depId and ArrivalPlaceID=@arvId", conn);
            command.Parameters.AddWithValue("@facId",sf.Facility.Id);
            command.Parameters.AddWithValue("@depId", sf.DepartureLocation.Id);
            command.Parameters.AddWithValue("@arvId", sf.ArrivalLocation.Id);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    Disconnect();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            return true;
        }
        return false;
    }
    public SupportedFacility GetSupportedFacility(int id)
    {
        SupportedFacility sf = new SupportedFacility();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ONE, conn);
            cmd.Parameters.AddWithValue("@id",id);
            if (Connect())
            {
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    sf.Facility.Id = dr.GetInt32(0);
                    sf.DepartureLocation.Id = dr.GetInt32(1);
                    sf.ArrivalLocation.Id = dr.GetInt32(2);
                    Disconnect();
                }
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return sf;
    }
    public int Insert(SupportedFacility sf)
    {
        if (IsExisted(sf)) return 0;
        int effectedRows = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT, conn);
            cmd.Parameters.AddWithValue("@facId",sf.Facility.Id);
            cmd.Parameters.AddWithValue("@depId", sf.DepartureLocation.Id);
            cmd.Parameters.AddWithValue("@arvId", sf.ArrivalLocation.Id);
            if (Connect())
            {
                effectedRows = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex) {
            message = ex.Message;
        }
        return effectedRows;
    }
    public int Delete(int id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE, conn);           
            cmd.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                effectedRows = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return effectedRows;
    }

    /// <summary>
    /// Get list of places that ware supported by a facility
    /// </summary>
    /// <param name="facId"></param>
    /// <returns></returns>
    public IList<SupportedFacility> GetSupportedPlaces(int facId)
    {
        IList<SupportedFacility> list = new List<SupportedFacility>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_FAC, conn);
            cmd.Parameters.AddWithValue("@facId", facId);
            if (Connect())
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SupportedFacility sf = new SupportedFacility();
                    sf.Id = dr.GetInt32(0);
                    sf.Facility.Name = dr.GetString(1);
                    sf.Facility.Price = dr.GetDecimal(2);
                    sf.ArrivalLocation.Name = dr.GetString(3);
                    sf.DepartureLocation.Name = dr.GetString(4);
                    list.Add(sf);
                }
                Disconnect();
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return list;
    }
}
